package jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;

public class ConnectDBQuery {
	public static void main(String[] args) throws Exception {
//		connectDBQuerytest();
		ddl();
		dml();
		sysdate();
	}

	private static void dml() {
		
	}

	private static void sysdate() throws Exception {
		String url="jdbc:oracle:thin:@172.16.1.230:1521:tarena";
		String driver="oracle.jdbc.driver.OracleDriver";
		String username="scott";
		String pwd="tiger";
		Class.forName(driver);
		
		Connection con = DriverManager.getConnection(url,username,pwd);
		
		Statement stm=null;
		ResultSet rs = null;
		stm = con.createStatement();
		rs = stm.executeQuery("select sysdate from dual");
		
		rs.next();
		//不同数据库有不同的时间表示，所以有以下三种
		Date d1 = rs.getDate(1);//年月日
		Time d2 = rs.getTime(1);//时分秒
		Timestamp d3 = rs.getTimestamp(1);//年月日时分秒
		System.out.print(rs.getString("sysdate"));//getDate(),getTime(),getTimestamp
		rs.close();
		stm.close();
		con.close();
	}

	private static void ddl() throws Exception {
		String url="jdbc:oracle:thin:@172.16.1.230:1521:tarena";
		String driver="oracle.jdbc.driver.OracleDriver";
		String username="scott";
		String pwd="tiger";
		Class.forName(driver);
		
		Connection con = DriverManager.getConnection(url,username,pwd);
		System.out.println(con);
		
		Statement stm=null;
		ResultSet rs = null;
		stm = con.createStatement();
		rs = stm.executeQuery("select id ,first_name from s_emp");
		rs.setFetchSize(100);
		
		while(rs.next()){
			System.out.print("id:"+rs.getString(1));
			System.out.println(",first_name:"+rs.getString(2));
		}
		rs.close();
		stm.close();
		con.close();
	}

	private static void connectDBQuerytest() throws ClassNotFoundException, SQLException {
		String url="jdbc:oracle:thin:@172.16.1.230:1521:tarena";
		String driver="oracle.jdbc.driver.OracleDriver";
		String username="scott";
		String pwd="tiger";
		Class.forName(driver);
//		Class.forName("com.mysql.jdbc");
		
		Connection con = DriverManager.getConnection(url,username,pwd);
		System.out.println(con);
		
		Statement stm=null;
		ResultSet rs = null;
		stm = con.createStatement();
		rs = stm.executeQuery("select id ,first_name from s_emp");
		rs.setFetchSize(100);
		
		while(rs.next()){
			System.out.print("id:"+rs.getString(1));
			System.out.println(",first_name:"+rs.getString(2));
		}
		System.out.println("****************");
		rs = stm.executeQuery("select id ,first_name from s_emp where id = 2");
		while(rs.next()){
			System.out.print("id:"+rs.getString(1));
			System.out.println(",first_name:"+rs.getString(2));
		}
		rs.close();
		stm.close();
		System.out.println("****************");
		PreparedStatement pstm = null;
		ResultSet prs = null;
		pstm= con.prepareStatement("select id,first_name,salary from s_emp where id = ?");
		pstm.setString(1, "2");//id=2
		prs = pstm.executeQuery();
		while(prs.next()){
			System.out.print("id:"+prs.getString("id"));//getString方法为将id取出做为String类型，其他同理
			System.out.print(",first_name:"+prs.getString(2));
			System.out.println(",salary:"+prs.getInt(3));
		}
		prs.close();
		pstm.close();
		con.close();
	}
}
